// Package migrate provides a simple filesystem-based SQL migration runner for PostgreSQL.
package migrate

import (
    "bufio"
    "errors"
    "fmt"
    "os"
    "path/filepath"
    "sort"
    "strings"
    "time"

    "github.com/we-pay-kit/we-pay-kit/internal/db"
)

// ApplyMigrations runs all pending migrations in the given directory.
// Migrations are applied in lexicographical order. Each file should be a valid SQL script.
func ApplyMigrations(migrationsDir string) error {
    database := db.GetDB()
    if database == nil {
        return errors.New("database not initialized")
    }

    if err := ensureSchemaMigrationsTable(database); err != nil {
        return fmt.Errorf("ensure schema_migrations: %w", err)
    }

    files, err := os.ReadDir(migrationsDir)
    if err != nil {
        return fmt.Errorf("read migrations dir: %w", err)
    }

    // Collect *.sql files
    sqlFiles := make([]string, 0, len(files))
    for _, f := range files {
        if f.IsDir() { continue }
        name := f.Name()
        if strings.HasSuffix(strings.ToLower(name), ".sql") {
            sqlFiles = append(sqlFiles, name)
        }
    }
    sort.Strings(sqlFiles)

    for _, name := range sqlFiles {
        version := strings.SplitN(name, "_", 2)[0]
        applied, err := isApplied(database, version)
        if err != nil { return fmt.Errorf("check applied %s: %w", name, err) }
        if applied { continue }

        path := filepath.Join(migrationsDir, name)
        sqlText, err := readSQLFile(path)
        if err != nil { return fmt.Errorf("read %s: %w", name, err) }

        // Apply within transaction
        tx, err := database.Begin()
        if err != nil { return fmt.Errorf("begin tx: %w", err) }
        if _, err := tx.Exec(sqlText); err != nil {
            _ = tx.Rollback()
            return fmt.Errorf("apply %s: %w", name, err)
        }
        if _, err := tx.Exec("INSERT INTO schema_migrations(version, applied_at) VALUES($1, $2)", version, time.Now()); err != nil {
            _ = tx.Rollback()
            return fmt.Errorf("record version %s: %w", name, err)
        }
        if err := tx.Commit(); err != nil { return fmt.Errorf("commit %s: %w", name, err) }
    }
    return nil
}

func ensureSchemaMigrationsTable(database *db.DB) error {
    _, err := database.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
        version VARCHAR(64) PRIMARY KEY,
        applied_at TIMESTAMP NOT NULL
    )`)
    return err
}

func isApplied(database *db.DB, version string) (bool, error) {
    var v string
    err := database.QueryRow("SELECT version FROM schema_migrations WHERE version=$1", version).Scan(&v)
    if err != nil {
        // no rows
        if strings.Contains(err.Error(), "no rows") { return false, nil }
        return false, err
    }
    return true, nil
}

func readSQLFile(path string) (string, error) {
    f, err := os.Open(path)
    if err != nil { return "", err }
    defer f.Close()
    var b strings.Builder
    r := bufio.NewScanner(f)
    // Increase buffer for long SQL lines
    const maxCapacity = 1024 * 1024
    buf := make([]byte, 64*1024)
    r.Buffer(buf, maxCapacity)
    for r.Scan() {
        line := r.Text()
        // Skip standalone COMMIT statements to avoid nested tx conflicts
        if strings.EqualFold(strings.TrimSpace(line), "COMMIT;") { continue }
        b.WriteString(line)
        b.WriteString("\n")
    }
    if err := r.Err(); err != nil { return "", err }
    return b.String(), nil
}


